from sqlalchemy.orm import Session
from sqlalchemy import or_, text
from fastapi import HTTPException, status
from typing import List, Optional

from ..models.note import Note, NoteHistory
from ..models.tag import Tag
from ..schemas.note import NoteCreate, NoteUpdate


class NoteService:
    """笔记服务"""
    
    @staticmethod
    def get_notes(
        user_id: int,
        db: Session,
        skip: int = 0,
        limit: int = 100,
        notebook_id: Optional[int] = None,
        tag_id: Optional[int] = None,
        is_pinned: Optional[bool] = None
    ) -> tuple[List[Note], int]:
        """获取笔记列表"""
        query = db.query(Note).filter(Note.user_id == user_id)
        
        # 按笔记本筛选
        if notebook_id is not None:
            query = query.filter(Note.notebook_id == notebook_id)
        
        # 按标签筛选
        if tag_id is not None:
            query = query.join(Note.tags).filter(Tag.id == tag_id)
        
        # 按置顶筛选
        if is_pinned is not None:
            query = query.filter(Note.is_pinned == is_pinned)
        
        # 计算总数
        total = query.count()
        
        # 排序和分页
        notes = query.order_by(Note.is_pinned.desc(), Note.updated_at.desc())\
                     .offset(skip).limit(limit).all()
        
        return notes, total
    
    @staticmethod
    def get_note(note_id: int, user_id: int, db: Session) -> Note:
        """获取单个笔记"""
        note = db.query(Note).filter(
            Note.id == note_id,
            Note.user_id == user_id
        ).first()
        
        if not note:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail="笔记不存在"
            )
        
        return note
    
    @staticmethod
    def create_note(note_data: NoteCreate, user_id: int, db: Session) -> Note:
        """创建笔记"""
        # 创建笔记
        note = Note(
            user_id=user_id,
            title=note_data.title,
            content=note_data.content,
            notebook_id=note_data.notebook_id,
            is_pinned=note_data.is_pinned
        )
        
        # 添加标签
        if note_data.tag_ids:
            tags = db.query(Tag).filter(
                Tag.id.in_(note_data.tag_ids),
                Tag.user_id == user_id
            ).all()
            note.tags = tags
        
        db.add(note)
        db.commit()
        db.refresh(note)
        
        # 创建初始版本历史
        history = NoteHistory(
            note_id=note.id,
            content=note.content,
            version=1
        )
        db.add(history)
        db.commit()
        
        return note
    
    @staticmethod
    def update_note(note_id: int, note_data: NoteUpdate, user_id: int, db: Session) -> Note:
        """更新笔记"""
        note = NoteService.get_note(note_id, user_id, db)
        
        # 保存旧内容到历史
        if note_data.content and note_data.content != note.content:
            last_version = db.query(NoteHistory)\
                            .filter(NoteHistory.note_id == note_id)\
                            .order_by(NoteHistory.version.desc())\
                            .first()
            
            new_version = (last_version.version + 1) if last_version else 1
            history = NoteHistory(
                note_id=note.id,
                content=note_data.content,
                version=new_version
            )
            db.add(history)
        
        # 更新笔记字段
        update_data = note_data.model_dump(exclude_unset=True, exclude={'tag_ids'})
        for field, value in update_data.items():
            setattr(note, field, value)
        
        # 更新标签
        if note_data.tag_ids is not None:
            tags = db.query(Tag).filter(
                Tag.id.in_(note_data.tag_ids),
                Tag.user_id == user_id
            ).all()
            note.tags = tags
        
        db.commit()
        db.refresh(note)
        return note
    
    @staticmethod
    def delete_note(note_id: int, user_id: int, db: Session) -> None:
        """删除笔记"""
        note = NoteService.get_note(note_id, user_id, db)
        db.delete(note)
        db.commit()
    
    @staticmethod
    def search_notes(keyword: str, user_id: int, db: Session, limit: int = 50) -> List[Note]:
        """搜索笔记"""
        # 使用 LIKE 进行简单搜索（生产环境建议使用 MySQL 全文索引或 Elasticsearch）
        search_pattern = f"%{keyword}%"
        notes = db.query(Note).filter(
            Note.user_id == user_id,
            or_(
                Note.title.like(search_pattern),
                Note.content.like(search_pattern)
            )
        ).order_by(Note.updated_at.desc()).limit(limit).all()
        
        return notes
    
    @staticmethod
    def get_recommended_notes(note_id: int, user_id: int, db: Session, limit: int = 5) -> List[Note]:
        """获取推荐笔记（基于标签相似度）"""
        # 获取当前笔记的标签
        current_note = NoteService.get_note(note_id, user_id, db)
        if not current_note.tags:
            return []
        
        tag_ids = [tag.id for tag in current_note.tags]
        
        # 查找有相同标签的其他笔记
        notes = db.query(Note)\
                  .filter(Note.user_id == user_id, Note.id != note_id)\
                  .join(Note.tags)\
                  .filter(Tag.id.in_(tag_ids))\
                  .distinct()\
                  .limit(limit)\
                  .all()
        
        return notes

